/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package wads.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLNonTransientConnectionException;
import java.sql.Statement;
import java.util.Properties;
import wads.com.UserInfo;
import wads.com.UserInfoDataStruct;
import wads.com.WADTools;

/**
 *
 * @author ManowTangdud
 */
public class Derby {
    private String driver = "";
    private String protocol = "";
    private String server = "";
    private String user = "admin";
    private String password = "admin";
    private String schema = "app.";
    private String database = "WadsDB";
    private int framework = 1;
    private Connection conn;
    private Properties prop;
    private boolean dbServerOn;

    public static final int CLIENT_FRAMEWORK = 1;
    public static final int EMBEDDED_FRAMEWORK = 2;

    public static final String POWER_USER = "pu", RESTRICT_USER = "ru", ADMIN = "admin";

    public Derby() {

//        prop = WADTools.loadWadConfig();
//        // load config
//        if(prop != null) {
//            server = prop.getProperty(WADTools.DBSERVER_NAME_KEYNAME)+"/";
//        }

        startServer();
    }

    public Derby(int framework) {
        this.framework = framework;
        if(framework == CLIENT_FRAMEWORK) {
            driver = "org.apache.derby.jdbc.ClientDriver";
            protocol = "jdbc:derby://";
            server = "localhost:1527/";
        }
        else {
            driver = "org.apache.derby.jdbc.EmbeddedDriver";
            protocol = "jdbc:derby:";
            server = "databases/";
        }

//        prop = WADTools.loadWadConfig();
//        // load config
//        if(prop != null) {
//            server = prop.getProperty(WADTools.DBSERVER_NAME_KEYNAME)+"/";
//        }

        startServer();
    }

    private void startServer() {

        try {
            // load driver
            Class.forName(driver).newInstance();
            System.out.println("Loaded the appropriate driver.");

            conn = DriverManager.getConnection(protocol+server+database+";user="+user+";password="+password);

            setDbServerOn(true);
        }
        catch (ClassNotFoundException cnfe) {
            System.err.println("\nUnable to load the JDBC driver " + driver);
            System.err.println("Please check your CLASSPATH.");
            cnfe.printStackTrace(System.err);
            setDbServerOn(false);
        }
        catch (InstantiationException ie) {
            System.err.println("\nUnable to instantiate the JDBC driver " + driver);
            ie.printStackTrace(System.err);
            setDbServerOn(false);
        }
        catch (IllegalAccessException iae) {
            System.err.println("\nNot allowed to access the JDBC driver " + driver);
            iae.printStackTrace(System.err);
            setDbServerOn(false);
        }
        catch (SQLNonTransientConnectionException ex) {
            System.err.println("\nCannot connect to the database " + server);
            ex.printStackTrace(System.err);
            setDbServerOn(false);
        }
        catch (SQLException sqlex) {
            System.err.println("\nUnable process querying.");
            sqlex.printStackTrace(System.err);
            setDbServerOn(false);
        }
    }

    public UserInfo loadUsers() {
        UserInfo members = new UserInfo();
        UserInfoDataStruct member;

        try {
            // start query process
            Statement stmt = conn.createStatement();
            String query = "SELECT * FROM "+schema+"users";
            System.out.println("Query String: "+query);
            ResultSet rs = stmt.executeQuery(query);

            while(rs.next()) {
                member = new UserInfoDataStruct();
                System.out.println(rs.getInt("uid"));
                member.setId(rs.getInt("uid"));
                System.out.println(rs.getString("username"));
                member.setUsername(rs.getString("username"));
                System.out.println(rs.getBytes("password"));
                member.setEncryptedPassword(rs.getBytes("password"));
                System.out.println(rs.getString("name"));
                member.setName(rs.getString("name"));
                System.out.println(rs.getString("position"));
                member.setPosition(rs.getString("position"));
                System.out.println(rs.getString("email"));
                member.setEMail(rs.getString("email"));
                System.out.println(rs.getString("roles")+"\n");
                member.setRoles(rs.getString("roles"));

                members.addData(member);
            }
            stmt.close();

            return members;
        }
        catch (SQLException sqlex) {
            System.err.println("\nUnable process querying.");
            sqlex.printStackTrace(System.err);
        }

        return null;
    }

    // flag parameter can be 'pu' = Powered User, 'ru' = Restricted User, 'admin' = Administrator
    public UserInfo loadUsers(String flag) {
        UserInfo members = new UserInfo();
        UserInfoDataStruct member;

        try {
            // load config
            if(prop != null) {
                server = prop.getProperty(WADTools.DBSERVER_NAME_KEYNAME)+"/";
            }

            // start query process
            Statement stmt = conn.createStatement();
            String query = "SELECT * FROM "+schema+"users WHERE roles = '"+flag+"'";
            System.out.println("Query String: "+query);
            ResultSet rs = stmt.executeQuery(query);

            while(rs.next()) {
                member = new UserInfoDataStruct();
                System.out.println(rs.getInt("uid"));
                member.setId(rs.getInt("uid"));
                System.out.println(rs.getString("username"));
                member.setUsername(rs.getString("username"));
                System.out.println(rs.getBytes("password"));
                member.setEncryptedPassword(rs.getBytes("password"));
                System.out.println(rs.getString("name"));
                member.setName(rs.getString("name"));
                System.out.println(rs.getString("position"));
                member.setPosition(rs.getString("position"));
                System.out.println(rs.getString("email"));
                member.setEMail(rs.getString("email"));
                System.out.println(rs.getString("roles")+"\n");
                member.setRoles(rs.getString("roles"));

                members.addData(member);
            }
            stmt.close();

            return members;
        }
        catch (SQLException sqlex) {
            System.err.println("\nUnable process querying.");
            sqlex.printStackTrace(System.err);
        }

        return null;
    }

    public UserInfoDataStruct loadUser(String username) {

        UserInfoDataStruct member;

        try {
            // start query process
            Statement stmt = conn.createStatement();
            String query = "SELECT * FROM "+schema+"users WHERE username = '"+username+"'";
            System.out.println("Query String: "+query);
            ResultSet rs = stmt.executeQuery(query);

            rs.next();
            member = new UserInfoDataStruct();
            System.out.println(rs.getInt("uid"));
            member.setId(rs.getInt("uid"));
            System.out.println(rs.getString("username"));
            member.setUsername(rs.getString("username"));
            System.out.println(rs.getBytes("password"));
            member.setEncryptedPassword(rs.getBytes("password"));
            System.out.println(rs.getString("name"));
            member.setName(rs.getString("name"));
            System.out.println(rs.getString("position"));
            member.setPosition(rs.getString("position"));
            System.out.println(rs.getString("email"));
            member.setEMail(rs.getString("email"));
            System.out.println(rs.getString("roles")+"\n");
            member.setRoles(rs.getString("roles"));

            stmt.close();

            return member;
        }
        catch (SQLException sqlex) {
            System.err.println("\nUnable process querying.");
            sqlex.printStackTrace(System.err);
        }

        return null;
    }

    public void insertUser(UserInfoDataStruct userinfo) {

        try {
            // start query process
            String query = "INSERT INTO "+schema+"users (username,password,name,position,email,roles) VALUES (?,?,?,?,?,?)";
            PreparedStatement stmt = conn.prepareStatement(query);

            System.out.println(userinfo.toString());
            stmt.setString(1, userinfo.getUsername());
            stmt.setBytes(2, userinfo.getEncryptedPassword());
            stmt.setString(3, userinfo.getName());
            stmt.setString(4, userinfo.getPosition());
            stmt.setString(5, userinfo.getEMail());
            stmt.setString(6, userinfo.getRoles());

            stmt.execute();

            stmt.close();
        }
        catch (SQLException sqlex) {
            System.err.println("\nUnable process querying.");
            sqlex.printStackTrace(System.err);
        }
    }

    public void updateUser(UserInfoDataStruct userinfo) {

        try {
            // start query process
            String query = "UPDATE "+schema+"users SET username=?,password=?,name=?,position=?,email=?,roles=? WHERE uid=?";
            PreparedStatement stmt = conn.prepareStatement(query);

            System.out.println(userinfo.toString());
            stmt.setString(1, userinfo.getUsername());
            stmt.setBytes(2, userinfo.getEncryptedPassword());
            stmt.setString(3, userinfo.getName());
            stmt.setString(4, userinfo.getPosition());
            stmt.setString(5, userinfo.getEMail());
            stmt.setString(6, userinfo.getRoles());
            stmt.setInt(7, userinfo.getId());

            stmt.execute();

            stmt.close();
        }
        catch (SQLException sqlex) {
            System.err.println("\nUnable process querying.");
            sqlex.printStackTrace(System.err);
        }
    }

    public void deleteUser(int uid) {

        try {
            // start query process
            String query = "DELETE FROM "+schema+"users WHERE uid="+uid;
            System.out.println("Query String: "+query);
            Statement stmt = conn.createStatement();

            stmt.execute(query);

            stmt.close();
        }
        catch (SQLException sqlex) {
            System.err.println("\nUnable process querying.");
            sqlex.printStackTrace(System.err);
        }
    }

    public boolean isDuplicated(String username) {

        try {
            // start query process
            Statement stmt = conn.createStatement();
            String query = "SELECT * FROM "+schema+"users WHERE username = '"+username+"'";
            System.out.println("Query String: "+query);
            ResultSet rs = stmt.executeQuery(query);

            if(rs.next()) {
                return true;
            }

            stmt.close();

            return false;
        }
        catch (SQLException sqlex) {
            System.err.println("\nUnable process querying.");
            sqlex.printStackTrace(System.err);
        }

        return false;
    }

    /**
     * @return the serverOn
     */
    public boolean isDbServerOn() {
        return dbServerOn;
    }

    /**
     * @param serverOn the serverOn to set
     */
    public void setDbServerOn(boolean serverOn) {
        this.dbServerOn = serverOn;
    }

    /**
     * @return the framework
     */
    public int getFramework() {
        return framework;
    }

    /**
     * @param framework the framework to set
     */
    public void setFramework(int framework) {
        this.framework = framework;
        
        if(framework == CLIENT_FRAMEWORK) {
            driver = "org.apache.derby.jdbc.ClientDriver";
            protocol = "jdbc:derby://";
            server = "localhost:1527/";
        }
        else {
            driver = "org.apache.derby.jdbc.EmbeddedDriver";
            protocol = "jdbc:derby:";
            server = "databases/";
        }
    }

}
